﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Windows;
using ProMedical.Properties;

namespace ProMedical
{
    /// <summary>
    /// Interaction logic for App.xaml
    /// </summary>
    public partial class App : Application
    {
        private readonly SqlConnection _sqlCon = new SqlConnection();
        private SqlCommand _sqlCmd = new SqlCommand();


        public App()
        {
            if (SetupDatabase() == false)
            {
                return;
            }
        }


        public bool SetupDatabase()
        {
            bool bContinue = false;

            //Create a connection to SQL Server
            try
            {
                _sqlCon.ConnectionString = "Server=.\\sqlexpress;Integrated Security=true";
                _sqlCon.Open();
            }
            catch (SqlException exception)
            {
                MessageBox.Show("Fail to connect to SQL Server Express\n"
                                + exception.Number + " " + exception.Message);
                return false;
            }

            //Now that you are connected to Express, check the database 

            switch (CheckVersion())
            {
                case (int) VersionCheck.Equal:
                    {
                        bContinue = true;
                        break;
                    }
                case (int) VersionCheck.Failed:
                    {
                        bContinue = false;
                        break;
                    }
                case (int) VersionCheck.DatabaseIsOlder:
                    {
                        //Run the upgrade script
                        bContinue =
                            RunScript(Resource1.UpdateNorthwind2);
                        break;
                    }
                case (int) VersionCheck.DatabaseIsNewer:
                    {
                        bContinue = false;
                        break;
                    }
                case (int) VersionCheck.DatabaseNotFound:
                    {
                        //Run the creation script
                        bContinue =
                            RunScript(Resource1.CreateNorthwind2);
                        break;
                    }
                default:
                    {
                        bContinue = false;
                        break;
                    }
            }


            return bContinue;
        }

        public bool RunScript(string strFile)
        {
            string[] strCommands;
            strCommands = ParseScriptToCommands(strFile);
            try
            {
                if (_sqlCon.State != ConnectionState.Open) _sqlCon.Open();

                _sqlCmd.Connection = _sqlCon;

                foreach (string strCmd in strCommands)
                {
                    if (strCmd.Length > 0)
                    {
                        _sqlCmd.CommandText = strCmd;
                        _sqlCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(sql_ex.Number + " " +
                                sql_ex.Message);
                return false;
            }

            return true;
        }

        public int CheckVersion()
        {
            //Get Version information from application
            var v = new Version("1.0.0.1");

            try
            {
                //Verify that the AdventureWorks Database exists
                _sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name = 'Northwind2'",_sqlCon);
                string strResult = _sqlCmd.ExecuteScalar().ToString();
                if (strResult == "0")
                {
                    _sqlCon.Close();
                    return (int) VersionCheck.DatabaseNotFound;
                }

                _sqlCmd = new SqlCommand("SELECT Version from Northwind2..VersionInfo", _sqlCon);
                strResult = (string) _sqlCmd.ExecuteScalar();

                var vDb = new Version(strResult);

                _sqlCon.Close();

                if (vDb == v)
                    return (int) VersionCheck.Equal;

                if (vDb > v)
                    return (int) VersionCheck.DatabaseIsNewer;

                if (vDb < v)
                    return (int) VersionCheck.DatabaseIsOlder;
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(sql_ex.Number + " " +
                                sql_ex.Message);
                return (int) VersionCheck.Failed;
            }
            catch (Exception system_ex)
            {
                MessageBox.Show(system_ex.Message);
                return (int) VersionCheck.Failed;
            }

            return (int) VersionCheck.Failed;
        }

        public string[] ParseScriptToCommands(string strScript)
        {
            string[] commands;
            commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
            return commands;
        }

        #region Nested type: VersionCheck

        private enum VersionCheck
        {
            Failed = 0,
            Equal,
            DatabaseIsNewer,
            DatabaseIsOlder,
            DatabaseNotFound
        } ;

        #endregion
    }
}